/*
 * CDataStore.cpp
 *
 *  Created on: 2017年3月5日
 *      Author: chuanjiang.zh
 */

#include "CDataStore.h"
#include <assert.h>
#include <stdio.h>
#include "CLog.h"
#include "TStringUtil.h"
#include "TSafeStr.h"
#include "TStringJoiner.h"


static int callback(void * /*NotUsed*/, int argc, char **argv, char **azColName){

    int i;
    for(i=0; i<argc; i++){
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}


CDataStore::CDataStore():
	m_sqlite()
{

}

CDataStore::~CDataStore()
{
	close();
}

bool CDataStore::open(const std::string& path)
{
	comn::AutoCritSec lock(m_cs);

    sqlite3_open(path.c_str(), &m_sqlite);
    if (m_sqlite)
    {
        createTables();
    }

    return (m_sqlite != NULL);
}

void CDataStore::close()
{
	comn::AutoCritSec lock(m_cs);

    if (isOpen())
    {
        sqlite3_close(m_sqlite);
        m_sqlite = NULL;
    }
}

bool CDataStore::isOpen()
{
	return (m_sqlite != NULL);
}

bool CDataStore::addRecord(av::MRecord& record)
{
	std::ostringstream oss;
	oss << "insert into Record values(null";

	oss << ",'" << record.device << "'";
	oss << "," << record.startTime;
	oss << "," << record.stopTime;
	oss << ",'" << record.filename << "'";
	oss << ",'" << record.filepath << "'";
	oss << "," << record.filesize;
	oss << "," << record.duration;
	oss << ",'" << record.url << "'";
	oss << "," << record.events;
	oss << "," << record.type;
	oss << ",'" << record.recorder << "'";
	oss << ",'" << record.memo << "'";

	oss << ")";

	std::string sql = oss.str();
	return insert(sql, record.id);
}

bool CDataStore::updateRecord(const av::MRecord& record)
{
	std::ostringstream oss;
	oss << "update record set ";

	comn::StringJoiner joiner("", ", ");

	joiner.add("stop_time=", record.stopTime);
	joiner.add("file_size=", record.filesize);
	joiner.add("duration=", record.duration);
	joiner.add("events=", record.events);

	oss << joiner.str();

	if (record.id != 0)
	{
		oss << " where id=" << record.id;
	}
	else
	{
		oss << " where file_path='" << record.filepath << "'";
	}

	std::string sql = oss.str();
	return execute(sql);
}

bool CDataStore::removeRecord(int id)
{
    std::string sql = comn::StringUtil::format("delete from Record where id='%d'", id);
    return execute(sql);
}

int CDataStore::searchRecord(const av::MRecordQuery& query,	int offset, av::MRecord* records, int size)
{
	std::ostringstream oss;
	oss << "select * from record ";

	comn::StringJoiner joiner("where ", " and ");
	if (query.device && strlen(query.device) > 0)
	{
		joiner.add("device=", query.device);
	}

	if (query.fromTime != 0)
	{
		joiner.add("start_time >= ", query.fromTime);
	}

	if (query.toTime != 0)
	{
		joiner.add("start_time < ", query.toTime);
	}

	if (query.recordType <= 0)
	{
		/// pass
	}
	else
	{
	    joiner.add("`type` = ", query.recordType);
	}

    if (query.recorder && strlen(query.recorder) > 0)
    {
        joiner.add("recorder=", query.recorder);
    }

	if (joiner.size() > 0)
	{
		oss << joiner.str();
	}

	oss << " order by start_time ";
	oss << " limit " << size;

    if (offset >= 0)
    {
        oss << " offset " << offset;
    }

	std::string sql = oss.str();
	return queryRecords(sql, records, size);
}

int CDataStore::countRecord(const av::MRecordQuery& query)
{
    std::ostringstream oss;
    oss << "select count(*) from record ";

    comn::StringJoiner joiner("where ", " and ");
    if (query.device && strlen(query.device) > 0)
    {
        joiner.add("device=", query.device);
    }

    if (query.fromTime != 0)
    {
        joiner.add("start_time >= ", query.fromTime);
    }

    if (query.toTime != 0)
    {
        joiner.add("start_time < ", query.toTime);
    }

    if (query.recordType <= 0)
    {
        /// pass
    }
    else
    {
        joiner.add("`type` = ", query.recordType);
    }

    if (query.recorder && strlen(query.recorder) > 0)
    {
        joiner.add("recorder=", query.recorder);
    }

    if (joiner.size() > 0)
    {
        oss << joiner.str();
    }

    std::string sql = oss.str();
    return queryInt(sql, 0);
}

bool CDataStore::removeRecords(const char* device, time_t fromTime, time_t toTime, int events)
{
	std::ostringstream oss;
	oss << "delete  record ";

	comn::StringJoiner joiner("where ", " and ");
	if (device && strlen(device) > 0)
    {
        joiner.add("device=", device);
    }

	if (fromTime != 0)
	{
		joiner.add("start_time >= ", fromTime);
	}

	if (toTime != 0)
	{
		joiner.add("start_time < ", toTime);
	}

	if (events < 0)
	{
		/// pass
	}
	else if (events == 0)
	{
		joiner.add("events = 0");
	}
	else
	{
		std::string cond = comn::StringUtil::format("(events&%d) != 0", events);
		joiner.add(cond);
	}

	if (joiner.size() > 0)
	{
		oss << joiner.str();
	}

	std::string sql = oss.str();
	return execute(sql);
}

bool CDataStore::getRecord(int id, av::MRecord& record)
{
	std::ostringstream oss;
	oss << "select * from record where id=" << id;
	std::string sql = oss.str();
	return queryRecord(sql, record);
}

bool CDataStore::findRecord(const char* filename, av::MRecord& record)
{
	std::string sql = comn::StringUtil::format("select * from record where file_name='%s'", filename);
	return queryRecord(sql, record);
}

bool CDataStore::addEvent(av::AlarmEvent& event)
{
	std::ostringstream oss;
	oss << "insert into EVENT values(null";
	oss << "," << event.type;
	oss << ",'" << event.device << "'";
	oss << "," << event.startTime;
	oss << ",'" << event.name << "'";
	oss << ",'" << event.memo << "'";
	oss << ")";

	std::string sql = oss.str();
	return insert(sql, event.id);
}

bool CDataStore::removeEvent(int id)
{
	std::string sql = comn::StringUtil::format("delete from Event where id='%d'", id);
	return execute(sql);
}

int CDataStore::searchEvent(const char* device, time_t fromTime, time_t toTime, int events,
	 av::AlarmEvent* alarmEvents, int size)
{
	std::ostringstream oss;
	oss << "select * from event ";

	comn::StringJoiner joiner("where ", " and ");
    if (device && strlen(device) > 0)
    {
        joiner.add("device=", device);
    }

	if (fromTime != 0)
	{
		joiner.add("start_time >= ", fromTime);
	}

	if (toTime != 0)
	{
		joiner.add("start_time < ", toTime);
	}

	if (events < 0)
	{
		/// pass
	}
	else if (events == 0)
	{
		/// pass
	}
	else
	{
		std::string cond = comn::StringUtil::format("(events&%d) != 0", events);
		joiner.add(cond);
	}

	if (joiner.size() > 0)
	{
		oss << joiner.str();
	}

	oss << " order by start_time ";
	oss << "limit " << size;

	std::string sql = oss.str();
	return queryEvents(sql, alarmEvents, size);
}

bool CDataStore::removeEvents(const char* device, time_t fromTime, time_t toTime, int events)
{
	std::ostringstream oss;
	oss << "select * from event ";

	comn::StringJoiner joiner("where ", " and ");
    if (device && strlen(device) > 0)
    {
        joiner.add("device=", device);
    }

	if (fromTime != 0)
	{
		joiner.add("start_time >= ", fromTime);
	}

	if (toTime != 0)
	{
		joiner.add("start_time < ", toTime);
	}

	if (events < 0)
	{
		/// pass
	}
	else if (events == 0)
	{
		///
	}
	else
	{
		std::string cond = comn::StringUtil::format("(events&%d) != 0", events);
		joiner.add(cond);
	}

	if (joiner.size() > 0)
	{
		oss << joiner.str();
	}

	std::string sql = oss.str();
	return execute(sql);
}

bool CDataStore::getEvent(int id, av::AlarmEvent& event)
{
	std::ostringstream oss;
	oss << "select * from event where id=" << id;
	std::string sql = oss.str();
	return queryEvent(sql, event);
}

int CDataStore::listRecord(const char* device, int offset, av::MRecord* records, int size)
{
	std::ostringstream oss;
	oss << "select * from record ";

	comn::StringJoiner joiner("where ", " and ");
    if (device && strlen(device) > 0)
    {
        joiner.add("device=", device);
    }

	if (joiner.size() > 0)
	{
		oss << joiner.str();
	}

	oss << " order by start_time ";
	oss << " limit " << size;
	oss << " offset " << offset;

	std::string sql = oss.str();
	return queryRecords(sql, records, size);
}

bool CDataStore::execute(const std::string& sql)
{
	comn::AutoCritSec lock(m_cs);

    if (!m_sqlite)
    {
        return false;
    }

    bool done = false;
    char *zErrMsg = 0;
    int rc = sqlite3_exec(m_sqlite, sql.c_str(), callback, NULL, &zErrMsg);
    if( rc != SQLITE_OK )
    {
    	CLog::warning("CDataStore::execute. sql:%s. rc:%d, %s\n", sql.c_str(), rc, zErrMsg);
    	sqlite3_free(zErrMsg);
    }
    else
    {
        done = true;
    }
    return done;
}

int CDataStore::queryInt(const std::string& sql, int defValue)
{
	comn::AutoCritSec lock(m_cs);

    if (!m_sqlite)
    {
        return defValue;
    }

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(m_sqlite, sql.c_str(), -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        return defValue;
    }

    int value = defValue;
    rc = sqlite3_step(stmt);
    if (rc == SQLITE_ROW)
    {
        value = sqlite3_column_int(stmt, 0);
    }

    sqlite3_finalize(stmt);

    return value;
}

bool CDataStore::insert(const std::string& sql, int& autoId)
{
	bool ret = execute(sql);

	comn::AutoCritSec lock(m_cs);
	if (ret && m_sqlite)
	{
		autoId = (int)sqlite3_last_insert_rowid(m_sqlite);
	}
	return ret;
}

bool CDataStore::insert(const std::string& sql, int64_t& autoId)
{
	bool ret = execute(sql);

	comn::AutoCritSec lock(m_cs);
	if (ret && m_sqlite)
	{
		autoId = sqlite3_last_insert_rowid(m_sqlite);
	}
	return ret;
}

bool CDataStore::queryRecord(const std::string& sql, av::MRecord& record)
{
	comn::AutoCritSec lock(m_cs);

	if (!m_sqlite)
	{
		return false;
	}

	sqlite3_stmt *stmt;
	int rc = sqlite3_prepare_v2(m_sqlite, sql.c_str(), -1, &stmt, NULL);
	if (rc != SQLITE_OK)
	{
		return false;
	}

	bool found = false;
	rc = sqlite3_step(stmt);
	if (rc == SQLITE_ROW)
	{
		int col = 0;

		toRecord(stmt, record);

		found = true;
	}

	sqlite3_finalize(stmt);

	return found;
}

bool CDataStore::queryEvent(const std::string& sql, av::AlarmEvent& event)
{
	comn::AutoCritSec lock(m_cs);

	if (!m_sqlite)
	{
		return false;
	}

	sqlite3_stmt *stmt;
	int rc = sqlite3_prepare_v2(m_sqlite, sql.c_str(), -1, &stmt, NULL);
	if (rc != SQLITE_OK)
	{
		return false;
	}

	bool found = false;
	rc = sqlite3_step(stmt);
	if (rc == SQLITE_ROW)
	{
		int col = 0;

		toEvent(stmt, event);

		found = true;
	}

	sqlite3_finalize(stmt);

	return found;
}

int CDataStore::queryRecords(const std::string& sql, av::MRecord* records, int size)
{
	comn::AutoCritSec lock(m_cs);

    if (!m_sqlite)
    {
        return false;
    }

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(m_sqlite, sql.c_str(), -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        return false;
    }

    int count = 0;
    for (;;)
    {
    	if (count >= size)
		{
			break;
		}

        rc = sqlite3_step(stmt);
        if (rc == SQLITE_DONE)
        {
            break;
        }

        if (rc == SQLITE_ROW)
        {
            int col = 0;
            av::MRecord& record = records[count];

            toRecord(stmt, record);

            count ++;
        }
        else
        {
            break;
        }
    }

    sqlite3_finalize(stmt);

    return count;
}

int CDataStore::queryEvents(const std::string& sql, av::AlarmEvent* events, int size)
{
	comn::AutoCritSec lock(m_cs);

    if (!m_sqlite)
    {
        return false;
    }

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(m_sqlite, sql.c_str(), -1, &stmt, NULL);
    if (rc != SQLITE_OK)
    {
        return false;
    }

    int count = 0;
    for (;;)
    {
    	if (count >= size)
		{
			break;
		}

        rc = sqlite3_step(stmt);
        if (rc == SQLITE_DONE)
        {
            break;
        }

        if (rc == SQLITE_ROW)
        {
            int col = 0;
            av::AlarmEvent& event = events[count];

            toEvent(stmt, event);

            count ++;
        }
        else
        {
            break;
        }
    }

    sqlite3_finalize(stmt);

    return count;
}

void CDataStore::createTables()
{
    const char* sqlUser =
    		"CREATE TABLE if not exists Record("\
    			 "ID INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL,"\
    			 "DEVICE VARCHAR(45) NOT NULL,"\
    			 "START_TIME BIGINT NOT NULL DEFAULT (strftime('%s','now', 'localtime')),"\
    			 "STOP_TIME BIGINT,"\
    			 "FILE_NAME VARCHAR(255),"\
    			 "FILE_PATH VARCHAR(255),"\
    			 "FILE_SIZE BIGINT,"\
    			 "DURATION INTEGER,"\
    			 "URL VARCHAR(255),"\
    			 "EVENTS INTEGER,"\
    			 "`type` INTEGER NULL DEFAULT 0,"\
    			 "recorder VARCHAR(45) NULL DEFAULT '',"\
				 "memo VARCHAR(255) NULL DEFAULT ''"\
    		");";

    execute(sqlUser);

    const char* sqlGroup =
    		"CREATE TABLE if not exists Event("\
    			 "ID INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL,"\
    			 "TYPE INTEGER NOT NULL,"\
    			 "DEVICE VARCHAR(45) NOT NULL,"\
    			 "START_TIME BIGINT NOT NULL DEFAULT (strftime('%s','now', 'localtime')),"\
    			 "NAME VARCHAR(255),"\
    			 "MEMO VARCHAR(511)"\
    		");";

    execute(sqlGroup);

}

void CDataStore::toRecord(sqlite3_stmt *stmt, av::MRecord& record)
{
    int col = 0;

    record.id = sqlite3_column_int(stmt, col ++);
    comn::copyStr(record.device, (const char*)sqlite3_column_text(stmt, col ++));
    record.startTime = sqlite3_column_int64(stmt, col ++);;
    record.stopTime = sqlite3_column_int64(stmt, col ++);
    comn::copyStr(record.filename,(const char*)sqlite3_column_text(stmt, col ++));
    comn::copyStr(record.filepath,(const char*)sqlite3_column_text(stmt, col ++));
    record.filesize = sqlite3_column_int64(stmt, col ++);
    record.duration = sqlite3_column_int(stmt, col ++);
    comn::copyStr(record.url, (const char*)sqlite3_column_text(stmt, col ++));
    record.events = sqlite3_column_int(stmt, col ++);
    record.type = sqlite3_column_int(stmt, col ++);
    comn::copyStr(record.recorder, (const char*)sqlite3_column_text(stmt, col++));
	comn::copyStr(record.memo, (const char*)sqlite3_column_text(stmt, col++));
}

void CDataStore::toEvent(sqlite3_stmt *stmt, av::AlarmEvent& event)
{
	int col = 0;
    event.id = sqlite3_column_int(stmt, col ++);
    event.type = sqlite3_column_int(stmt, col ++);
    comn::copyStr(event.device, (const char*)sqlite3_column_text(stmt, col ++));
    event.startTime = sqlite3_column_int64(stmt, col ++);
    comn::copyStr(event.name, (const char*)sqlite3_column_text(stmt, col ++));
    comn::copyStr(event.memo, (const char*)sqlite3_column_text(stmt, col ++));
}
